using System;
using System.Collections.Generic;
using System.IO;
using System.Data.Common;
using System.Data;
using RikMigrations.Providers;

namespace RikMigrations
{
	public abstract class DbProvider : IDisposable
	{
		protected DbProvider(string filename)
		{
			if (!string.IsNullOrEmpty(filename))
			{
				var dir = Path.GetDirectoryName( System.Reflection.Assembly.GetExecutingAssembly().Location );
				var path = Path.Combine( dir, filename );
				Console.WriteLine("Outputting SQL to '{0}'", path);
				outputFile = File.CreateText( path );
			}
            else
				outputFile = new StringWriter();
		}

		protected TextWriter outputFile { get; private set; }

		#region Basic Commands
		static string defaultConnectionString;
		public static string DefaultConnectionString
		{
			get { return defaultConnectionString ?? System.Configuration.ConfigurationManager.ConnectionStrings[0].ConnectionString; }
			set { defaultConnectionString = value; }
		}

		string connectionString;
		public string ConnectionString
		{
			get { return connectionString ?? DefaultConnectionString; }
			set { connectionString = value; }
		}

        private List<Command> commands = new List<Command>();
	    public List<Command> Commands
	    {
	        get { return commands; }
	        set { commands = value; }
	    }

		DbConnection transactedConnection;
		DbTransaction transaction;
		int transactionCount; // NOTE: This is really just for testing, shouldn't be used in production

		#region Properties
		private int? commandTimeout;
		public int? CommandTimeout
		{
			get { return commandTimeout; }
			set { commandTimeout = value; }
		}
		#endregion

        public void InitialiseDb()
        {
            var tableExistsCommand = GetTableExistsCommand("ModuleVersions");
            tableExistsCommand.Execute(this);
            if (!tableExistsCommand.Result)
            {
                var table = new Table(this, "ModuleVersions", true);
                table.AddColumn<string>("ModuleName", 64).PrimaryKey().NotNull();
                table.AddColumn<int>("CurrentVersion").Default(0).NotNull();
                this.ExecuteCommands();
            }
        }

	    public void CreateTransaction()
		{
			transactedConnection = CreateConnection();
			if ((transaction == null) || (transaction.Connection == null))
			{
				transaction = transactedConnection.BeginTransaction();
				transactionCount = 1;
			}
			else
				transactionCount++;
		}

		public void CommitTransaction()
		{
			if (--transactionCount == 0)
			{
			    transaction.Commit();
			    disposeTransaction();
                Commands.Clear();
			}
		}

	    private void disposeTransaction()
	    {
	        transaction = null;
	        transactedConnection.Dispose();
	        transactedConnection = null;
            Commands.Clear();
	    }

	    public void RollbackTransaction()
		{
			if (transaction != null)
			{
				transaction.Rollback();
                disposeTransaction();
            }
			if (--transactionCount > 0)
				throw new Exception("Inner transaction failed");
		}

	    protected internal DbConnection CreateConnection()
		{
			if (transactedConnection != null)
				return transactedConnection;
			return CreateNewConnection();
		}

		protected abstract DbConnection CreateNewConnection();
		protected abstract DbParameter CreateParameter(int i, object value);

		public void ExecuteNonQuery(string sql, params object[] parameters)
		{
			DbConnection conn = CreateConnection();
			try
			{
				ExecuteNonQuery(conn, sql, parameters);
			}
			catch( Exception ex )
			{
				throw new Exception( sql + "\n" + ex.Message, ex );
			}
			finally
			{
				if (transactedConnection == null)
					conn.Dispose();
			}
		}

		public void ExecuteNonQuery(DbConnection conn, string sql, params object[] parameters)
		{
			using (DbCommand cmd = conn.CreateCommand())
			{
				cmd.CommandText = sql;
				cmd.Connection = conn;
				if (CommandTimeout != null)
					cmd.CommandTimeout = CommandTimeout.Value;
				if (transactedConnection != null)
					cmd.Transaction = transaction;

				for (int i = 0; i < parameters.Length; i++)
				{
					if (parameters[i] == null)
						parameters[i] = DBNull.Value;
					cmd.Parameters.Add(CreateParameter(i, parameters[i]));
				}

				outputFile.WriteLine( cmd.CommandText );
				outputFile.WriteLine( "GO" + Environment.NewLine );
				cmd.ExecuteNonQuery();
			}
		}

		public object ExecuteScalar(string sql, params object[] parameters)
		{
			DbConnection conn = CreateConnection();
			try
			{
				return ExecuteScalar(conn, sql, parameters);
			}
			finally
			{
				if (transactedConnection == null)
					conn.Dispose();
			}
		}

		public object ExecuteScalar(DbConnection conn, string sql, params object[] parameters)
		{
			using (DbCommand cmd = conn.CreateCommand())
			{
				cmd.CommandText = sql;
				cmd.Connection = conn;
				if (CommandTimeout != null)
					cmd.CommandTimeout = CommandTimeout.Value;
				if (transactedConnection != null)
					cmd.Transaction = transaction;

				for (int i = 0; i < parameters.Length; i++)
				{
					if (parameters[i] == null)
						parameters[i] = DBNull.Value;
					cmd.Parameters.Add(CreateParameter(i, parameters[i]));
				}

				return cmd.ExecuteScalar();
			}
		}

		public DbDataReader ExecuteReader(string sql, params object[] parameters)
		{
			DbConnection conn = CreateConnection();
			using (DbCommand cmd = conn.CreateCommand())
			{
				cmd.CommandText = sql;
				cmd.Connection = conn;
				if (CommandTimeout != null)
					cmd.CommandTimeout = CommandTimeout.Value;
				if (transactedConnection != null)
					cmd.Transaction = transaction;

				for (int i = 0; i < parameters.Length; i++)
				{
					if (parameters[i] == null)
						parameters[i] = DBNull.Value;
					cmd.Parameters.Add(CreateParameter(i, parameters[i]));
				}

				return cmd.ExecuteReader(transactedConnection == null
					 ? CommandBehavior.CloseConnection : CommandBehavior.Default);
			}
		}

		public DbDataReader ExecuteReader(DbConnection conn, string sql, params object[] parameters)
		{
			using (DbCommand cmd = conn.CreateCommand())
			{
				cmd.CommandText = sql;
				cmd.Connection = conn;
				if (CommandTimeout != null)
					cmd.CommandTimeout = CommandTimeout.Value;
				if (transactedConnection != null)
					cmd.Transaction = transaction;

				for (int i = 0; i < parameters.Length; i++)
				{
					if (parameters[i] == null)
						parameters[i] = DBNull.Value;
					cmd.Parameters.Add(CreateParameter(i, parameters[i]));
				}

				return cmd.ExecuteReader();
			}
		}
		
        #endregion

	    public abstract int GetModuleVersion(string moduleName);
        public abstract void SetModuleVersion(string moduleName, int version);

		#region Commands

	    protected internal abstract Command GetCreateTableCommand(Table table);
	    protected internal abstract Command GetAddColumnCommand(Column column);
        protected internal abstract Command GetDropColumnCommand(Table table, string columnName);
        protected internal abstract Command GetAddForeignKeyCommand(ForeignKey key);
		protected internal abstract Command GetAddCompoundForeignKeyCommand( CompoundForeignKey key );
        protected internal abstract Command GetRenameTableCommand(Column column, string newName);
        protected internal abstract Command GetCreateViewCommand(View view);
        protected internal abstract Command GetCreateSPCommand(StoredProcedure sp);
        protected internal abstract Command GetInsertCommand(string tableName, object objectToInsert, bool IsIdentityInsert);
        protected internal abstract Command GetChangeDataTypeCommand(string tableName, string columnName, Type newColumnType, int length);

		#endregion

		#region Utilities
		protected virtual string GetLiteral(Type type, object value)
		{
			if (value.GetType() == typeof(string) && type != typeof(string))
				return (string)value;

			if (type == typeof(bool))
				return ((bool)value) ? "1" : "0";
		
            if (type == typeof(Guid) ||
		        type == typeof(string))
		        return "'" + value + "'";

		    return value.ToString();
		}
		#endregion

        [Obsolete("Use new expression libraries instead.", false)]
        public void InsertDataInto(string tableName, object objectToInsert, bool isIdentityInsert)
	    {
            GetInsertCommand(tableName, objectToInsert, isIdentityInsert).Execute(this);
	    }


	    public void ExecuteCommands()
	    {
            foreach (Command cmd in Commands)
                cmd.Execute(this);
            Commands.Clear();
	    }

		public virtual string SchemaName{ get; set; }

		protected internal abstract Command GetAddSchemaCommand();
		protected internal abstract Command GetDropSchemaCommand();
	    protected internal abstract Command GetDropTableCommand(string tableName);
	    protected internal abstract Command GetDropViewCommand(string viewName);
	    public abstract IntrospectionCommand<bool> GetTableExistsCommand(string tableName);
	    public abstract IntrospectionCommand<bool> GetViewExistsCommand(string viewName);
	    public abstract IntrospectionCommand<bool> GetStoredProcedureExists(string name);
	    public abstract IntrospectionCommand<bool> GetColumnExists(string tableName, string columnName);
	    public abstract IntrospectionCommand<List<ForeignKey>> GetFindForeignKeys(Table tableName);

		public void Dispose()
		{
			outputFile.Close();
		}
	}
}
